package reports

import (
	"bytes"
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"time"

	"github.com/pkg/errors"
	"shqsoft.cn/pas3ui/base"
	"shqsoft.cn/pas3ui/popedom"

	"gorm.io/gorm"
)

// DataRange 数据范围
type DataRange int

// iota 初始化后会自动递增
const (
	AllRange DataRange = iota
	Dept1Range
	Dept2Range
	UserRange
)

type subTitle struct {
	colIdx        int
	captionColIdx int
	suffix        string
}

// Report 接口
type Report interface {
	SetParams(paramVals map[string]string, where []base.WhereCause, order map[string]string, calcs map[string][]string, colsDisplay map[string]bool, canIdno bool, unit int) error
	SetDataRange(popedomTypes []string, dRange DataRange, whereAttach string, groupByIndex int, uid int, istm bool, vals ...int) error
	GetConfig() *Config
	GetDataList(int, int) ([]map[string]string, int, error)
	GetRemarks() (string, string, error)
	GetAnnoDataList(int, int) ([]map[string]string, int, error)
	GetColumns(isAll bool) ([]Column, error)
	GetAnnoColumns() ([]Column, error)
	CreateReptData() error
	ExportXslx() (*bytes.Buffer, string, error)
	ParamsToString() string
}

// NewReport 新报表对象
func NewReport(name string) (rept Report, err error) {
	var config *Config
	if config, err = GetReportConfig(name); err != nil {
		return
	}
	for _, col := range config.Columns {
		if col.Format != "" {
			if col.DataType == "float" || col.DataType == "int" || col.DataType == "number" {
				if !strings.HasPrefix(col.Format, ".") || (!strings.HasSuffix(col.Format, "f") && !strings.HasSuffix(col.Format, "f%")) {
					return nil, fmt.Errorf("报表列%s的format定义的%s是无效！", col.Name, col.Format)
				}
			}
		}
	}
	klen := len(config.KeyColumns)
	keyColIds := make([]int, klen)
	for i := 0; i < klen; i++ {
		keyColIds[i] = -1
	}
	if config.Type == "list" || config.Type == "chart" || config.Type == "chartList" || config.Type == "cross" {
		rep := new(ListReport)
		rep.name = name
		rep.config = config
		if config.DataSource.DbName == "ch" {
			rep.db = base.CHDB
		} else if config.DataSource.DbName == "my" {
			rep.db = base.MYDB
		} else {
			err = fmt.Errorf("指定的数据库名%s不存在,数据库名称必须为ch或者my", config.DataSource.DbName)
			return
		}
		if len(config.Groups) > 0 && !config.NotFrontGroup {
			rep.groupByIndex = 1
		}
		rep.leafCols, err = config.GetLeafColumns()
		if err != nil {
			return
		}
		if config.Type == "cross" {
			rept = &CrossReport{ListReport: *rep}
		} else {
			rept = rep
		}
	} else {
		err = fmt.Errorf("报表类型%s错误，请指定list，chart，chartList或者cross", config.Type)
	}
	return
}

// ListReport 简单列表式报表
type ListReport struct {
	name           string
	paramVals      map[string]interface{}
	config         *Config
	tableName      string
	whereStr       string
	whereParamVals []interface{}
	orderBy        []string
	dataRange      DataRange
	rangeVals      []int
	canIdno        bool
	db             *gorm.DB
	leafCols       []*Column
	groupByIndex   int
	calcs          map[string][]string
	colsDisplay    map[string]bool
	unit           int
	days           int
	popedomTypes   []string
	remarks        string
	annoTableName  string
	uid            int
	istm           bool
}

func (lr *ListReport) getTitle(tsql string) string {
	tstr := strings.TrimSpace(tsql)
	var title string
	if len(tstr) > 6 && strings.ToLower(tstr[0:6]) == "select" {
		for _, p := range lr.config.Params {
			pname := "@" + p.Name
			tstr = strings.ReplaceAll(tstr, pname, base.ToString(lr.paramVals[p.Name]))
		}
		base.CHDB.Raw(tstr).Row().Scan(&title)
	} else {
		title = tstr
	}
	return title
}
func (lr *ListReport) GetColumns(isAll bool) (selCols []Column, err error) {
	selCols = make([]Column, 0, len(lr.config.Columns))
	var loop func(*Column) []Column
	loop = func(col *Column) []Column {
		isHas := true
		clen := len(col.Children)
		if !isAll {
			if clen == 0 && !lr.colsDisplay[col.Name] {
				isHas = false
			}
		} else {
			isHas = lr.parsePopedomTypes(col.PopedomTypes, col.PopedomRelation)
		}
		if !isHas {
			return nil
		}
		if clen > 0 {
			title := lr.getTitle(col.Title)
			ncol := Column{
				Title:    title,
				Children: make([]Column, 0, 5),
			}
			for i := 0; i < clen; i++ {
				cs := loop(&col.Children[i])
				if len(cs) > 0 {
					ncol.Children = append(ncol.Children, cs...)
				}
			}
			if len(ncol.Children) > 0 {
				return []Column{ncol}
			}
			return nil
		}
		if lr.groupByIndex > 0 && !lr.config.NotFrontGroup {
			find := false
			for _, cName := range lr.config.Groups[lr.groupByIndex-1].ExcludeCols {
				if col.Name == cName {
					find = true
					break
				}
			}
			if find {
				return nil
			}
		}
		cs := make([]Column, 0, 3)
		col2 := col.Clone()
		unit := col.Unit
		if col.DataType == "float" || col.DataType == "int" {
			if unit == 0 {
				unit = lr.config.Unit
			}
			col2.Unit = unit
		}
		col2.Title = lr.getTitle(col.Title)
		cs = append(cs, *col2)
		if calcs, ok := lr.calcs[col.Name]; ok {
			for _, calc := range calcs {
				name := fmt.Sprintf("%s_%s", col.Name, calc[1:])
				ccol := Column{
					Name:      name,
					Title:     lr.config.GetCalcTitle(calc),
					DataType:  col.DataType,
					Format:    col.Format,
					DataIndex: name,
					Dispaly:   true,
					Filter:    true,
					Sorter:    true,
					Width:     col.Width,
					Align:     col.Align,
					RefCol:    col.Name,
					Ellipsis:  true,
					Unit:      unit,
				}
				cs = append(cs, ccol)
			}
		}
		return cs
	}
	for _, col := range lr.config.Columns {
		cs := loop(&col)
		if len(cs) > 0 {
			selCols = append(selCols, cs...)
		}
	}
	return
}
func (lr *ListReport) GetAnnoColumns() (selCols []Column, err error) {
	selCols = lr.config.Annotations.Columns
	clen := len(selCols)
	for i := 0; i < clen; i++ {
		selCols[i].DataIndex = selCols[i].Name
	}
	return
}
func (lr *ListReport) paramsToSubHead() string {
	var buff bytes.Buffer
	for _, p := range lr.config.Params {
		val := lr.paramVals[p.Name]
		if len(p.DataItems) > 0 {
			for _, item := range p.DataItems {
				if item.Value == val {
					val = item.Label
				}
			}
		}
		buff.WriteString(p.Caption)
		buff.WriteString(":")
		buff.WriteString(base.ToString(val))
		buff.WriteString("        ")
	}
	buff.WriteString(lr.config.UnitTitle)
	buff.WriteString("       ")
	buff.WriteString(lr.config.Remarks)
	return buff.String()
}
func (lr *ListReport) ParamsToString() string {
	return lr.paramsToString()
}
func (lr *ListReport) paramsToString() string {
	var buff bytes.Buffer
	for _, p := range lr.config.Params {
		val := lr.paramVals[p.Name]
		if len(p.DataItems) > 0 {
			for _, item := range p.DataItems {
				if item.Value == val {
					val = item.Label
				}
			}
		}
		buff.WriteString(base.ToString(val))
		buff.WriteString("-")
	}
	blen := buff.Len()
	if blen > 0 {
		buff.Truncate(blen - 1)
	}
	return buff.String()
}

// SetDataRange 设置数据查看范围
func (lr *ListReport) SetDataRange(popedomTypes []string, dRange DataRange, whereAttach string, groupByIndex int, uid int, istm bool, vals ...int) (err error) {
	lr.popedomTypes = popedomTypes
	lr.dataRange = dRange
	lr.rangeVals = vals
	lr.uid = uid
	lr.istm = istm
	if len(lr.config.Groups) > 0 {
		lr.groupByIndex = groupByIndex
	} else {
		lr.groupByIndex = 0
	}
	rVals := lr.rangeVals
	fType := lr.config.AuthorityFilter.FilterType
	fColName := lr.config.AuthorityFilter.ColName
	var sqlStr string
	if glen := len(lr.config.Groups); glen > 0 {
		if glen < lr.groupByIndex {
			err = fmt.Errorf("给定的层次序号%d报表%s配置的层次数%d", lr.groupByIndex, lr.config.Name, glen)
			return
		}
		bys := lr.config.Groups[lr.groupByIndex-1].By
		if len(bys) == 0 {
			err = fmt.Errorf("没有为报表%s配置的第%d个层次配置by", lr.config.Name, lr.groupByIndex)
			return
		}
		fColName = bys[0]
		if fColName == "PDID" || fColName == "UTID" {
			fType = "dept1"
		} else if fColName == "DPID" {
			fType = "dept2"
		} else {
			fType = "user"
		}
	}
	if lr.dataRange == Dept2Range {
		if fType == "user" {
			sqlStr = "SELECT EMID FROM DICT_USER_DEPT WHERE DPID in (?)"
		}
	} else if lr.dataRange == Dept1Range {
		if fType == "user" {
			sqlStr = "SELECT EMID FROM DICT_USER_DEPT WHERE PDID in (?)"
		} else if fType == "dept2" {
			sqlStr = "SELECT DPID FROM DICT_DEPT WHERE PDID in (?)"
		}
	}
	if sqlStr != "" {
		rows, err := lr.db.Raw(sqlStr, rVals).Rows()
		if err != nil {
			return err
		}
		defer rows.Close()
		ids := make([]int, 0, 20)
		for rows.Next() {
			var id int
			rows.Scan(&id)
			ids = append(ids, id)
		}
		rVals = ids
	}
	if whereAttach != "" {
		if lr.whereStr == "" {
			lr.whereStr = whereAttach
		} else {
			lr.whereStr += " AND " + whereAttach
		}
	}
	if lr.dataRange != AllRange && fColName != "" && fType != "" {
		if fColName == "CUID" {
			fColName = "EMID"
		}
		if lr.whereStr == "" {
			lr.whereStr = fColName + " in (?)"
		} else {
			lr.whereStr = lr.whereStr + " and " + fColName + " in (?)"
		}
		if lr.whereParamVals == nil {
			lr.whereParamVals = make([]interface{}, 0, 5)
		}
		lr.whereParamVals = append(lr.whereParamVals, rVals)
	}
	return
}

// SetParams 设置报表参数
func (lr *ListReport) SetParams(paramVals map[string]string, where []base.WhereCause, order map[string]string, calcs map[string][]string, colsDisplay map[string]bool, canIdno bool, unit int) (err error) {
	lr.paramVals = make(map[string]interface{}, len(paramVals))
	var pVal string
	var ok bool
	for _, p := range lr.config.Params {
		if pVal, ok = paramVals[p.Name]; !ok && p.Grid > 0 {
			pVal = ""
			// return fmt.Errorf("参数名称%s在paramVals中没有找到！", p.Name)
		}
		if pVal == "" || (p.Name != "emplLevel" && pVal == "#all") {
			continue
		}
		if p.DataType == "date" {
			format := "2006-01-02"
			vlen := len(paramVals[p.Name])
			if vlen > 10 {
				format = time.RFC3339
			} else if vlen == 8 {
				format = "20060101"
			}
			var date time.Time
			if date, err = time.Parse(format, paramVals[p.Name]); err != nil {
				err = fmt.Errorf("为报表%s参数%s提供的值%s不是有效的日期格式，有效的日期格式为2021-01-02", lr.name, p.Name, paramVals[p.Name])
				return err
			}
			dType := "EDW"
			if strings.HasPrefix(lr.name, "ods_") || base.AppConf.MainDataSource == "ODS" {
				dType = "ODS"
			}
			var sysDate *time.Time
			sysDate, _, err = base.GetSYSDate(dType)
			if err != nil {
				return
			}
			dateStr := date.Format("2006-01-02")
			sysDateStr := sysDate.Format("2006-01-02")
			if dateStr > sysDateStr {
				if p.EditType == "month" || p.EditType == "quarter" {
					dateStr = sysDateStr
				} else {
					err = errors.Errorf("%s:%s不能大于系统日期%s", p.Name, dateStr, sysDate.Format("2006-01-02"))
					return
				}
			}
			if lr.config.MinDate != "" && p.Name == "sdate" && dateStr < lr.config.MinDate {
				// lr.paramVals[p.Name] = lr.config.MinDate
				err = errors.Errorf("报表开始日期:%s不能小于%s", dateStr, lr.config.MinDate)
				return
			}
			if lr.config.MaxDate != "" && p.Name == "edate" && dateStr > lr.config.MaxDate {
				// lr.paramVals[p.Name] = lr.config.MaxDate
				err = errors.Errorf("报表截止日期:%s不能大于%s", dateStr, lr.config.MaxDate)
				return
			}
			lr.paramVals[p.Name] = date
		} else if p.DataType == "int" {
			if lr.paramVals[p.Name], err = strconv.Atoi(paramVals[p.Name]); err != nil {
				err = fmt.Errorf("为报表%s参数%s提供的值%s不是有效的整数", lr.name, p.Name, paramVals[p.Name])
				return err
			}
		} else if p.DataType == "float" {
			if lr.paramVals[p.Name], err = strconv.Atoi(paramVals[p.Name]); err != nil {
				err = fmt.Errorf("为报表%s参数%s提供的值%s不是有效的数值", lr.name, p.Name, paramVals[p.Name])
				return err
			}
		} else {
			lr.paramVals[p.Name] = paramVals[p.Name]
		}
	}
	var buff bytes.Buffer
	if lr.config.Where != "" {
		buff.WriteString(lr.config.Where)
		buff.WriteString(" AND ")
	}
	wVals := make([]interface{}, 0, 5)
	for k, v := range lr.paramVals {
		var para *Parameter
		for _, p := range lr.config.Params {
			if p.Name == k {
				para = &p
				break
			}
		}
		if k == "emplID" {
			emplID := v.(int)
			if emplID == 0 {
				continue
			}
		}
		if para.RefColName != "" {
			buff.WriteString(para.RefColName)
			var val interface{}
			switch para.RelationMark {
			case "like":
				buff.WriteString(" like ?")
				val = "%" + base.ToString(v) + "%"
			case "leftlike":
				buff.WriteString(" like ?")
				val = base.ToString(v) + "%"
			case "rightlike":
				buff.WriteString(" like ?")
				val = "%" + base.ToString(v)
			default:
				buff.WriteString(para.RelationMark)
				buff.WriteString(" ? ")
				val = v
			}
			wVals = append(wVals, val)
			buff.WriteString(" AND ")
		}
	}
	var fStr string
	var fVals []interface{}
	if fStr, fVals, err = base.ParseWhereCause(where); err != nil {
		return
	}
	if len(fStr) > 0 {
		buff.WriteString(fStr)
		wVals = append(wVals, fVals...)
	} else {
		if buff.Len() > 0 {
			buff.Truncate(buff.Len() - 5)
		}
	}
	whereExp := buff.String()
	if whereExp != "" {
		if lr.whereStr == "" {
			lr.whereStr = buff.String()
		} else {
			lr.whereStr += " AND " + buff.String()
		}
	}
	if len(lr.whereParamVals) == 0 {
		lr.whereParamVals = wVals
	} else {
		lr.whereParamVals = append(lr.whereParamVals, wVals...)
	}
	if len(order) == 0 {
		if lr.config.OrderBy != "" {
			lr.orderBy = []string{lr.config.OrderBy}
		}
	} else {
		lr.orderBy = make([]string, 0, 3)
		for k, v := range order {
			if k == "" {
				continue
			}
			item := k + " " + v
			lr.orderBy = append(lr.orderBy, item)
		}
	}
	if len(lr.config.KeyColumns) == 0 {
		err = fmt.Errorf("报表%s的必须指定KeyColumns", lr.config.Name)
		return
	}
	lr.orderBy = append(lr.orderBy, lr.config.KeyColumns...)
	lr.canIdno = canIdno
	if lr.config.Type != "cross" && colsDisplay == nil {
		colsDisplay = make(map[string]bool, 50)
		cols := lr.leafCols
		for _, col := range cols {
			disp := col.Dispaly
			if disp {
				disp = lr.parsePopedomTypes(col.PopedomTypes, col.PopedomRelation)
			}
			colsDisplay[col.Name] = disp
		}
	}
	lr.colsDisplay = colsDisplay
	lr.calcs = calcs
	if lr.config.Type != "cross" {
		err = lr.bindColumns()
	}
	lr.unit = unit
	nextDate := false
	var sdatep, edatep *time.Time
	for _, p := range lr.config.Params {
		if p.DataType == "date" {
			date, ok := lr.paramVals[p.Name].(time.Time)
			if !ok {
				err = fmt.Errorf("生成报表%s数据出错，参数%s不是有效的日期", lr.config.Name, p.Name)
				return
			}
			if nextDate {
				edatep = &date
			} else {
				sdatep = &date
				nextDate = true
			}
		}
	}
	if sdatep != nil && edatep != nil {
		lr.days = int(edatep.Sub(*sdatep).Hours())/24 + 1
	} else {
		lr.days = 1
	}
	return
}
func (lr *ListReport) parsePopedomTypes(ptypes []string, rela string) (ok bool) {
	if len(ptypes) == 0 {
		return true
	}
	isor := false
	if strings.EqualFold(rela, "or") {
		isor = true
	}
	for _, tp := range ptypes {
		if base.InStringSlice(tp, lr.popedomTypes) {
			if isor {
				ok = true
				break
			}
			ok = true
		} else {
			if !isor {
				ok = false
				break
			}
		}
	}
	return
}

func (lr *ListReport) CreateReptData() (err error) {
	err = lr.createReptData()
	if err != nil {
		return
	}
	err = createCalcs(lr.config, lr.tableName, lr.paramVals, lr.calcs)
	return
}

// GetConfig 获取列定义
func (lr *ListReport) GetConfig() *Config {
	return lr.config
}

func (lr *ListReport) getCount() (count int, err error) {
	tName := lr.tableName
	var num int64
	db := lr.db.Table(tName).Where(lr.whereStr, lr.whereParamVals...)
	if lr.groupByIndex > 0 && !lr.config.NotFrontGroup {
		db = db.Where("gp_index = ?", lr.groupByIndex)
	}
	err = db.Count(&num).Error
	count = int(num)
	if lr.config.HasTotal {
		count++
	}
	return
}
func (lr *ListReport) getAnnoCount() (count int, err error) {
	tName := lr.annoTableName
	var num int64
	db := lr.db.Table(tName).Where("")
	err = db.Count(&num).Error
	count = int(num)
	return
}
func (lr *ListReport) getTotal(conds map[string]string) (tData map[string]string, err error) {
	tData = make(map[string]string)
	var eCols []string
	where := ""
	if len(lr.config.Groups) > 0 && lr.groupByIndex > 0 && !lr.config.NotFrontGroup {
		eCols = lr.config.Groups[lr.groupByIndex-1].ExcludeCols
		where = fmt.Sprintf("where gp_index=%d ", lr.groupByIndex)
	}
	sumCols := make([]string, 0, 16)
	scsIdx := make([]int, 0, 16)
	sumOk := false
	unitMap := make(map[int]int)
	calcNames := make([]string, 0, 5)
	refColIdxs := make([]int, 0, 5)
	for i, col := range lr.leafCols {
		if col.Unit > 0 {
			unitMap[i] = col.Unit
		}
		if len(eCols) > 0 && base.InStringSlice(col.Name, eCols) {
			continue
		}
		if condVal, ok := conds[col.Name]; ok {
			tData[col.DataIndex] = condVal + "小计"
		}
		if col.DataType == "string" {
			if len(conds) == 0 && !sumOk {
				tData[col.DataIndex] = "合计"
				sumOk = true
			} else {
				continue
			}
		} else if col.DataType == "date" || !lr.colsDisplay[col.Name] {
			continue
		} else if col.GroupExp == "none" {
			continue
		} else {
			if col.GroupExp == "" {
				sumCols = append(sumCols, fmt.Sprintf("sum(%s) as C%d", col.Name, i))
				scsIdx = append(scsIdx, i)
				if calcs, ok := lr.calcs[col.Name]; ok {
					for _, calc := range calcs {
						calcNames = append(calcNames, fmt.Sprintf("%s_%s", col.Name, calc[1:]))
						refColIdxs = append(refColIdxs, i)
					}
				}
			} else {
				exp := col.GroupExp
				if lr.config.Type == "cross" {
					exp = strings.ReplaceAll(exp, "#condexp", "1=1")
					idx := strings.LastIndex(col.Name, "_") + 1
					prev := col.Name[:idx]
					for _, cell := range lr.config.Cells {
						exp = strings.ReplaceAll(exp, cell.Name, prev+cell.Name)
					}
				}
				sumCols = append(sumCols, fmt.Sprintf("%s as C%d", strings.ReplaceAll(exp, "#days", strconv.Itoa(lr.days)), i))
				scsIdx = append(scsIdx, i)
			}
		}
	}
	for i, calc := range calcNames {
		sumCols = append(sumCols, fmt.Sprintf("sum(%s) as CB%d", calc, i))
	}
	if lr.whereStr != "" {
		if where == "" {
			where = fmt.Sprintf("where %s", lr.whereStr)
		} else {
			where = fmt.Sprintf("%s AND %s", where, lr.whereStr)
		}
	}
	if len(sumCols) == 0 {
		return
	}
	condStr := ""
	if len(conds) > 0 {
		for k, v := range conds {
			if len(condStr) == 0 {
				condStr = fmt.Sprintf("%s='%s'", k, v)
			} else {
				condStr = fmt.Sprintf("%s AND %s='%s'", condStr, k, v)
			}
		}
		if where == "" {
			where = fmt.Sprintf("where %s", condStr)
		} else {
			where = fmt.Sprintf("%s AND %s", where, condStr)
		}
	}
	sqlStr := fmt.Sprintf("select %s from %s %s", strings.Join(sumCols, ","), lr.tableName, where)
	clen := len(scsIdx) + len(calcNames)
	sumVals := make([]float64, clen)
	scanArgs := make([]interface{}, clen)
	for i := 0; i < clen; i++ {
		scanArgs[i] = &sumVals[i]
	}
	err = lr.db.Raw(sqlStr, lr.whereParamVals...).Row().Scan(scanArgs...)
	if err != nil {
		return
	}
	formatVal := func(colIdx int, val float64, colName string) {
		unit := unitMap[colIdx]
		if unit == 0 {
			unit = lr.config.Unit
		}
		if unit > 0 {
			val = val / float64(unit)
		}
		format := lr.leafCols[colIdx].Format
		if colName == "" {
			colName = lr.leafCols[colIdx].DataIndex
		}
		isP := strings.HasSuffix(format, "f%")
		if isP {
			format = format[:len(format)-1]
		}
		if lr.leafCols[colIdx].DataType == "float" {
			if format == "" {
				format = ".2f"
			}
			tData[colName] = fmt.Sprintf("%"+format, val)
		} else {
			tData[colName] = fmt.Sprintf("%.0f", val)
		}
	}
	for i, idx := range scsIdx {
		formatVal(idx, sumVals[i], "")
	}
	k := len(scsIdx)
	for i, calc := range calcNames {
		idx := refColIdxs[i]
		formatVal(idx, sumVals[k+i], calc)
	}
	return
}
func (lr *ListReport) bindColumns() (err error) {
	for i := 0; i < len(lr.leafCols); i++ {
		lr.leafCols[i].DataIndex = lr.leafCols[i].Name
	}
	return
}
func (lr *ListReport) createReptData() (err error) {
	refs := lr.config.DataSource.RefRepts
	rlan := len(refs)
	if rlan > 0 {
		for i := 0; i < rlan; i++ {
			err = lr.createRefRept(&refs[i])
			if err != nil {
				return
			}
		}
	}
	tmpTableName := lr.config.DataSource.TmpTableName
	var table string
	table, err = lr.parseTable(lr.config.DataSource.Table)
	if err != nil {
		return
	}
	sfName := lr.config.DataSource.SqlFileName
	if tmpTableName != "" {
		lr.tableName, err = lr.parseTableName(tmpTableName, false)
		if err != nil {
			return
		}
		if lr.config.DataSource.Type == "table" || lr.config.DataSource.Type == "sql" {
			var isEx bool
			isEx, err = TableExists(lr.tableName)
			if err != nil {
				return
			}
			if !isEx {
				if sfName != "" {
					err = CreateDataBySql(lr.config, lr.tableName, lr.paramVals)
					if err != nil {
						return
					}
				}
				err = lr.createTmpTableData(table)
			}
		} else if lr.config.DataSource.Type == "func" {
			fStr := lr.config.DataSource.Table
			f, ok := createReptFuncPool[fStr]
			if !ok {
				err = fmt.Errorf("生成报表%s数据出错，函数%s在函数池中找不到", lr.name, fStr)
				return
			}
			err = f(lr.config, lr.tableName, lr.paramVals)
		} else {
			err = fmt.Errorf("报表%s配置错误，不能识别的数据源Type：%s,必须为table,sql或者func", lr.name, lr.config.DataSource.Type)
			return
		}
	} else {
		if sfName != "" {
			err = fmt.Errorf("报表%s配置错误，数据源配置了SqlFileName,必须设置TmpTableName", lr.name)
			return
		}
		lr.tableName = table
	}
	if err != nil {
		return
	}
	err = lr.bindColumns()
	if err != nil {
		return
	}
	if len(lr.config.Annotations.Columns) == 0 {
		return
	}
	tmpTableName = lr.config.Annotations.DataSource.TmpTableName
	table, err = lr.parseTable(lr.config.Annotations.DataSource.Table)
	if err != nil {
		return
	}
	if tmpTableName != "" {
		lr.annoTableName, err = lr.parseTableName(tmpTableName, false)
		if err != nil {
			return
		}
		if lr.config.Annotations.DataSource.Type == "table" || lr.config.Annotations.DataSource.Type == "sql" {
			err = lr.createAnnoTmpTableData(table)
		} else if lr.config.Annotations.DataSource.Type == "func" {
			err = fmt.Errorf("报表%s配置错误，附注数据源不支持func", lr.name)
			return
		} else {
			err = fmt.Errorf("报表%s配置错误，不能识别的数据源Type：%s,必须为table,sql或者func", lr.name, lr.config.DataSource.Type)
			return
		}
	} else {
		lr.annoTableName = table
	}
	if err != nil {
		return
	}
	return
}
func (lr *ListReport) createRefRept(ref *RefRept) (err error) {
	var rep Report
	rep, err = NewReport(ref.Name)
	if err != nil {
		return
	}
	var ok bool
	refRept, ok := rep.(*ListReport)
	if !ok {
		err = fmt.Errorf("报表%s的配置错误，被引用的表必须是ListReports", lr.config.Name)
		return
	}
	refParas := make(map[string]interface{}, 3)
	for k1, k2 := range ref.Params {
		if pv, ok := lr.paramVals[k2]; ok {
			refParas[k1] = pv
		} else {
			err = fmt.Errorf("报表%s所引用的报表%s的参数配置错误，参数%s不存在！", lr.config.Name, ref.Name, k2)
			return
		}
	}
	refRept.paramVals = refParas
	err = refRept.createReptData()
	return
}
func (lr *ListReport) createAnnoTmpTableData(table string) (err error) {
	var dbHelp *DataBase
	tName := lr.annoTableName
	conf := &Config{
		DataSource: lr.config.Annotations.DataSource,
		Columns:    lr.config.Annotations.Columns,
	}
	dbHelp, err = NewDataBase(tName, conf)
	if err != nil {
		return
	}
	var exist bool
	exist, err = dbHelp.createTable("Memory")
	if exist || err != nil {
		return
	}
	reptLock.Lock(tName)
	defer reptLock.Unlock(tName)
	err = dbHelp.insertData(table, "", nil, "")
	if err != nil {
		lr.db.Exec("drop table " + tName)
		return
	}
	return
}
func (lr *ListReport) createTmpTableData(table string) (err error) {
	var dbHelp *DataBase
	tName := lr.tableName
	if lr.config.Type == "cross" {
		tName += "_list"
	}
	reptLock.Lock(tName)
	defer reptLock.Unlock(tName)
	dbHelp, err = NewDataBase(tName, lr.config)
	if err != nil {
		return
	}
	var exist bool
	exist, err = dbHelp.createTable("Memory")
	if exist || err != nil {
		return
	}
	order := ""
	if lr.orderBy != nil {
		order = strings.Join(lr.orderBy, ",")
	}
	err = dbHelp.insertData(table, "", nil, order)
	if err != nil {
		lr.db.Exec("drop table " + tName)
		return
	}
	err = dbHelp.createGroupBy(lr.days)
	if err != nil {
		lr.db.Exec("drop table " + tName)
		return
	}
	return
}
func (lr *ListReport) getTableDatas(offset, limit int) (rows *sql.Rows, retCols []*Column, stColNames []string, err error) {
	colNames := make([]string, 0, 50)
	retCols = make([]*Column, 0, 20)
	cols := lr.leafCols
	for _, kCol := range lr.config.KeyColumns {
		find := false
		for _, col := range cols {
			if col.Name == kCol {
				retCols = append(retCols, col)
				find = true
				break
			}
		}
		if !find {
			err = fmt.Errorf("报表%s的配置错误，keyColumns中配置的字段%s名在columns配置中没有找到", lr.config.Name, kCol)
			return
		}
	}
	colNames = append(colNames, lr.config.KeyColumns...)
	for _, g := range lr.config.Groups {
		for _, byName := range g.By {
			if !base.IsInSlice(colNames, byName) {
				find := false
				for _, col := range cols {
					if col.Name == byName {
						retCols = append(retCols, col)
						find = true
						break
					}
				}
				if !find {
					err = fmt.Errorf("报表%s的配置错误，group:%s中配置的byName:%s在columns配置中没有找到", lr.config.Name, g.Title, byName)
					return
				}
				colNames = append(colNames, byName)
			}
		}
	}
	fName := lr.config.AuthorityFilter.ColName
	if fName != "" && !base.IsInSlice(colNames, fName) {
		find := false
		for _, col := range cols {
			if col.Name == fName {
				retCols = append(retCols, col)
				find = true
				break
			}
		}
		if !find {
			err = fmt.Errorf("报表%s的配置错误，AuthorityFilter.ColName中配置的字段%s名在columns配置中没有找到", lr.config.Name, fName)
			return
		}
		colNames = append(colNames, fName)
	}
	stColNames = make([]string, 0, 3)
	for _, col := range cols {
		if col.FrontMust || lr.colsDisplay[col.Name] {
			retCols = append(retCols, col)
			colNames = append(colNames, col.Name)
		}
		if col.Subtotal {
			stColNames = append(stColNames, col.Name)
		}
	}
	if len(stColNames) > 0 {
		limit++
	}
	calcNames := make([]string, 0, 5)
	for k, v := range lr.calcs {
		var refCol *Column
		for _, col := range cols {
			if col.Name == k {
				refCol = col
			}
		}
		for _, calc := range v {
			colName := fmt.Sprintf("%s_%s", k, calc[1:])
			retCols = append(retCols, &Column{Name: colName, DataType: "float", Unit: refCol.Unit})
			calcNames = append(calcNames, colName)
		}
	}
	var buff bytes.Buffer
	buff.WriteString("select ")
	buff.WriteString(strings.Join(colNames, ","))
	if len(calcNames) > 0 {
		buff.WriteString(",")
		buff.WriteString(strings.Join(calcNames, ","))
	}
	buff.WriteString(" from ")
	buff.WriteString(lr.tableName)
	if lr.config.DataSource.Table == "createDateRangeData" && lr.config.Type == "list" {
		buff.WriteString(" final ")
	}
	if lr.groupByIndex > 0 && !lr.config.NotFrontGroup {
		buff.WriteString(" where ")
		buff.WriteString("gp_index=")
		buff.WriteString(strconv.Itoa(lr.groupByIndex))
	}
	if lr.whereStr != "" {
		if lr.groupByIndex == 0 || lr.config.NotFrontGroup {
			buff.WriteString(" where ")
		} else {
			buff.WriteString(" and ")
		}
		buff.WriteString(lr.whereStr)
	}
	stlen := len(stColNames)
	if lr.orderBy != nil || stlen > 0 || lr.config.NotFrontGroup {
		buff.WriteString(" order by ")
		if stlen > 0 {
			buff.WriteString(strings.Join(stColNames, ","))
		}
		if lr.orderBy != nil {
			if stlen > 0 {
				buff.WriteString(",")
			}
			buff.WriteString(strings.Join(lr.orderBy, ","))
		}
		if lr.config.NotFrontGroup && len(lr.config.Groups) > 0 {
			if len(lr.orderBy) > 0 {
				buff.WriteString(",")
			}
			for _, group := range lr.config.Groups {
				buff.WriteString(strings.Join(group.By, ","))
				buff.WriteString(",")
			}
			buff.Truncate(buff.Len() - 1)
		}
	}
	if stlen > 1 {
		err = errors.New("目前仅支持一个subtotal")
		return
	}
	if limit > 0 {
		buff.WriteString(" limit ")
		buff.WriteString(strconv.Itoa(limit))
	}
	if offset != -1 {
		buff.WriteString(" offset ")
		buff.WriteString(strconv.Itoa(offset))
	}
	rows, err = lr.db.Raw(buff.String(), lr.whereParamVals...).Rows()
	return
}
func (lr *ListReport) getAnnoTableDatas(offset, limit int) (rows *sql.Rows, retCols []*Column, err error) {
	colNames := make([]string, 0, 10)
	retCols = make([]*Column, 0, 10)
	cols, err := lr.GetAnnoColumns()
	if err != nil {
		return
	}
	for i := 0; i < len(cols); i++ {
		colNames = append(colNames, cols[i].Name)
		retCols = append(retCols, &cols[i])
	}
	var buff bytes.Buffer
	buff.WriteString("select ")
	buff.WriteString(strings.Join(colNames, ","))
	buff.WriteString(" from ")
	buff.WriteString(lr.annoTableName)
	if lr.config.Annotations.OrderBy != "" {
		buff.WriteString(" order by ")
		buff.WriteString(lr.config.Annotations.OrderBy)
	}
	if limit > 0 {
		buff.WriteString(" limit ")
		buff.WriteString(strconv.Itoa(limit))
	}
	if offset != -1 {
		buff.WriteString(" offset ")
		buff.WriteString(strconv.Itoa(offset))
	}
	rows, err = lr.db.Raw(buff.String()).Rows()
	return
}
func (lr *ListReport) GetRemarks() (name, remarks string, err error) {
	name = lr.config.Name
	remarks = strings.TrimSpace(lr.config.Remarks)
	if len(remarks) < 7 {
		return
	}
	perv := remarks[:6]
	if strings.EqualFold(perv, "select") {
		var sqlStr string
		sqlStr, err = lr.parseTableName(remarks, true)
		if err != nil {
			return
		}
		err = base.CHDB.Raw(sqlStr).Row().Scan(&remarks)
		if err != nil {
			return name, "", nil
		}
	}
	return
}

// GetAnnoDataList 获取附注数据
func (lr *ListReport) GetAnnoDataList(offset, limit int) (datas []map[string]string, total int, err error) {
	if len(lr.config.Annotations.Columns) == 0 {
		return
	}
	total, err = lr.getAnnoCount()
	if err != nil {
		return
	}
	datas = make([]map[string]string, 0, limit)
	var rows *sql.Rows
	var retCols []*Column
	if rows, retCols, err = lr.getAnnoTableDatas(offset, limit); err != nil {
		return
	}
	defer rows.Close()
	clen := len(retCols)
	emidIdx := -1
	for i, c := range retCols {
		if strings.EqualFold(c.Name, "EMID") {
			emidIdx = i
			break
		}
	}
	values := make([]sql.NullString, clen)
	scanArgs := make([]interface{}, clen)
	for i := 0; i < clen; i++ {
		scanArgs[i] = &values[i]
	}
	for rows.Next() {
		err := rows.Scan(scanArgs...)
		if err != nil {
			return nil, 0, err
		}
		mapVal, err := lr.toMap(values, retCols, nil, emidIdx)
		if err != nil {
			return nil, 0, err
		}
		datas = append(datas, mapVal)
	}
	rows.Close()
	return datas, total, nil
}

// GetDataList 获取数据
func (lr *ListReport) GetDataList(offset, limit int) (datas []map[string]string, total int, err error) {
	if lr.config.DataSource.CacheType == "redis" {
		return readFromRedis(lr.config, lr.tableName, lr.unit)
	} else {
		total, err = lr.getCount()
		if err != nil {
			return
		}
		datas = make([]map[string]string, 0, limit)
		var rows *sql.Rows
		var retCols []*Column
		var stColNames []string
		if rows, retCols, stColNames, err = lr.getTableDatas(offset, limit); err != nil {
			return
		}
		defer rows.Close()
		clen := len(retCols)
		values := make([]sql.NullString, clen)
		scanArgs := make([]interface{}, clen)
		for i := 0; i < clen; i++ {
			scanArgs[i] = &values[i]
		}
		stMap := make(map[string]string)
		hasNext := false
		ri := 0
		var sts []subTitle
		sts, err = lr.getSubtitles(retCols)
		if err != nil {
			return
		}
		emidIdx := -1
		for i, c := range retCols {
			if strings.EqualFold(c.Name, "EMID") {
				emidIdx = i
				break
			}
		}
		for rows.Next() {
			ri++
			err := rows.Scan(scanArgs...)
			if err != nil {
				return nil, 0, err
			}
			mapVal, err := lr.toMap(values, retCols, sts, emidIdx)
			if err != nil {
				return nil, 0, err
			}
			hasNext = true
			if len(stMap) > 0 {
				iseq := true
				for _, cname := range stColNames {
					if stMap[cname] != mapVal[cname] {
						iseq = false
						break
					}
				}
				if !iseq {
					tData, err := lr.getTotal(stMap)
					if err != nil {
						return nil, 0, err
					}
					datas = append(datas, tData)
					hasNext = false
				}
			}
			if ri > limit {
				hasNext = false
				break
			}
			datas = append(datas, mapVal)
			for _, cname := range stColNames {
				stMap[cname] = mapVal[cname]
			}
		}
		if len(stMap) > 0 && hasNext {
			tData, err := lr.getTotal(stMap)
			if err != nil {
				return nil, 0, err
			}
			datas = append(datas, tData)
		}
		remain := total - offset
		if lr.config.HasTotal && remain <= limit {
			tData, err := lr.getTotal(nil)
			if err != nil {
				return nil, 0, err
			}
			datas = append(datas, tData)
		}
		rows.Close()
	}
	return datas, total, nil
}

// ExportXslx 导出Xslx格式
func (lr *ListReport) ExportXslx() (buff *bytes.Buffer, name string, err error) {
	xslx, err := NewXlsx()
	if err != nil {
		return nil, "", err
	}
	var count int
	count, err = lr.getCount()
	if err != nil {
		return
	}
	if count > base.AppConf.Ui.MaxExportNum {
		err = fmt.Errorf("要导出的数据有%d行,超过系统允许的最大可导出行数%d,请筛选数据后再导出", count, base.AppConf.Ui.MaxExportNum)
		return
	}
	selCols, _ := lr.GetColumns(false)
	xslx.SetColStart(2)
	xslx.AddColumns(selCols)
	xslx.AddTitle(lr.config.Name, 1)
	xslx.AddSubhead(lr.paramsToSubHead(), 2)
	var rows *sql.Rows
	var retCols []*Column
	var stColNames []string
	if rows, retCols, stColNames, err = lr.getTableDatas(-1, -1); err != nil {
		return
	}
	defer rows.Close()
	cols, err := base.GetColumns(rows)
	if err != nil {
		return nil, "", err
	}
	clen := len(cols)
	values := make([]sql.NullString, clen)
	scanArgs := make([]interface{}, clen)
	for i := 0; i < clen; i++ {
		scanArgs[i] = &values[i]
	}
	stMap := make(map[string]string)
	hasNext := false
	var sts []subTitle
	sts, err = lr.getSubtitles(retCols)
	if err != nil {
		return
	}
	emidIdx := -1
	zwColName := ""
	for i, c := range retCols {
		if strings.EqualFold(c.Name, "EMID") {
			emidIdx = i
		} else if c.Title == "职务" {
			zwColName = c.Name
		}
	}
	for rows.Next() {
		err := rows.Scan(scanArgs...)
		if err != nil {
			return nil, "", err
		}
		mapVal, err := lr.toMap(values, retCols, sts, emidIdx)
		if err != nil {
			return nil, "", err
		}
		if zwColName != "" && !base.IsInSlice(lr.popedomTypes, "4") {
			zw := mapVal[zwColName]
			emid, _ := strconv.Atoi(mapVal["EMID"])
			if len(zw) > 0 && emid > 0 {
				has, _ := popedom.IsHasRole(emid, 38)
				if has {
					continue
				}
			}
			// if strings.Contains(zw, "正职") || strings.Contains(zw, "负责人") || strings.Contains(zw, "主持") {
			// 	continue
			// }
		}
		hasNext = true
		if len(stMap) > 0 {
			iseq := true
			for _, cname := range stColNames {
				if stMap[cname] != mapVal[cname] {
					iseq = false
					break
				}
			}
			if !iseq {
				tData, err := lr.getTotal(stMap)
				if err != nil {
					return nil, "", err
				}
				if err = xslx.AddRow(tData); err != nil {
					return nil, "", err
				}
				hasNext = false
			}
		}
		if err = xslx.AddRow(mapVal); err != nil {
			return nil, "", err
		}
		for _, cname := range stColNames {
			stMap[cname] = mapVal[cname]
		}
	}
	if len(stMap) > 0 && hasNext {
		tData, err := lr.getTotal(stMap)
		if err != nil {
			return nil, "", err
		}
		if err = xslx.AddRow(tData); err != nil {
			return nil, "", err
		}
	}
	if lr.config.HasTotal {
		tData, err := lr.getTotal(nil)
		if err != nil {
			return nil, "", err
		}
		if err = xslx.AddRow(tData); err != nil {
			return nil, "", err
		}
	}
	buff = xslx.Export()
	name = lr.paramsToString()
	return
}
func (lr *ListReport) parseTableName(name string, isSql bool) (string, error) {
	if lr.paramVals == nil {
		return "", fmt.Errorf("解析报表%s的表名出错，参数没有初始化！", lr.config.Name)
	}
	format := "20060102"
	if isSql {
		format = "2006-01-02"
	}
	var pName string
	for k, v := range lr.paramVals {
		pName = "@" + k
		var val string
		dVal, ok := v.(time.Time)
		if ok {
			val = dVal.Format(format)
			if isSql {
				name = strings.Replace(name, "_"+pName, "_"+dVal.Format("20060102"), -1)
			}
		} else {
			val = base.ToString(v)
		}
		name = strings.Replace(name, pName, val, -1)
		if k == "cyno" && lr.config.DataSource.Type == "sql" {
			val := base.ToString(v)
			var cynoWhere string
			if val == "1" {
				cynoWhere = "1=1"
			} else if val == "2" {
				cynoWhere = "CYNO='01'"
			} else {
				cynoWhere = "CYNO<>'01'"
			}
			name = strings.Replace(name, "#CYNO", cynoWhere, -1)
		}
	}
	if strings.Contains(name, "@") || strings.Contains(name, "#") {
		return "", fmt.Errorf("报表%s的表名没有完全解析，解析之后是%s！", lr.config.Name, name)
	}
	return name, nil
}

func (lr *ListReport) parseTable(table string) (string, error) {
	if lr.config.DataSource.Type != "sql" {
		return table, nil
	}
	if !strings.Contains(table, "@") {
		if strings.Contains(table, "select") || strings.Contains(table, "SELECT") {
			return "(" + table + ") t ", nil
		}
		return table, nil
	}
	table, err := lr.parseTableName(table, true)
	if err != nil {
		return "", err
	}
	return "(" + table + ") t ", nil
}
func (lr *ListReport) getSubtitles(cols []*Column) (sts []subTitle, err error) {
	if !lr.config.NotFrontGroup {
		return
	}
	sts = make([]subTitle, 0, 5)
	glen := len(lr.config.Groups)
	for k := 0; k < glen-1; k++ {
		g := lr.config.Groups[k]
		st := subTitle{suffix: g.SubtitleSuffix}
		for i, c := range cols {
			if g.SubtitleCol == c.Name {
				st.colIdx = i
			} else if g.Caption == c.Name {
				st.captionColIdx = i
			}
		}
		if st.captionColIdx == 0 {
			err = fmt.Errorf("报表%s中的group:%s的caption:%s在字段列表中不存在", lr.config.Name, g.Title, g.Caption)
			return
		}
		if st.colIdx == 0 {
			err = fmt.Errorf("报表%s中的group:%s的subtitleCol:%s在字段列表中不存在", lr.config.Name, g.Title, g.SubtitleCol)
			return
		}
		sts = append(sts, st)
	}
	return
}
func (lr *ListReport) toMap(row []sql.NullString, cols []*Column, sts []subTitle, emidIdx int) (dataMap map[string]string, err error) {
	dataMap = make(map[string]string, 50)
	for i, col := range cols {
		var val string
		if row[i].Valid {
			val = row[i].String
		}
		dataType := col.DataType
		toVal := val
		if val != "" {
			format := col.Format
			if dataType == "float" {
				fVal, err := strconv.ParseFloat(val, 64)
				if err != nil {
					return nil, err
				}
				if format == "" {
					format = ".2f"
				}
				isP := strings.HasSuffix(format, "f%")
				if isP {
					num, err := strconv.Atoi(format[1 : len(format)-2])
					if err != nil {
						err = fmt.Errorf("列%s的format:%s无效", col.Name, format)
						return nil, err
					}
					format = fmt.Sprintf(".%df", num+2)
				} else {
					unit := lr.unit
					if unit == 0 {
						unit = col.Unit
					}
					if unit == 0 {
						unit = lr.config.Unit
					}
					if unit > 0 {
						fVal = fVal / float64(unit)
					}
				}
				toVal = fmt.Sprintf("%"+format, fVal)
			} else if dataType == "date" {
				dVal, err := time.Parse(time.RFC3339, val)
				if err != nil {
					return nil, err
				}
				if dVal.Year() <= 1970 {
					toVal = ""
				} else {
					if format == "" {
						format = "2006-01-02"
					}
					toVal = dVal.Format(col.Format)
				}
			} else if dataType == "bool" {
				bVal, err := strconv.ParseBool(val)
				if err != nil || !bVal {
					toVal = "否"
				} else {
					toVal = "是"
				}
			} else {
				if dataType == "string" && val == "0" {
					toVal = ""
				} else if !lr.canIdno {
					if col.Title == "证件号码" || col.Title == "证件编号" || col.Name == "IDNO" {
						toVal = base.IndoTM(val)
					} else if !lr.istm && (emidIdx == -1 || row[emidIdx].String != strconv.Itoa(lr.uid)) {
						if col.Title == "卡号" || col.Name == "CDNO" {
							toVal = base.CdnoTM(val)
						} else if col.Title == "账号" || col.Title == "帐号" || col.Name == "ACNO" {
							toVal = base.AcnoTM(val)
						} else if col.Title == "子账号" || col.Title == "子帐号" || col.Name == "BANO" {
							toVal = base.AcnoTM(val)
						} else if !strings.Contains(col.Title, "银行") && (strings.Contains(col.Title, "手机") || col.Title == "联系方式" || strings.Contains(col.Title, "电话")) {
							toVal = base.PhoneTM(val)
						}
					}
				}
				if base.AppConf.Ui.IsDemo {
					titles := []string{"证件号码", "证件编号", "IDNO", "客户名称", "客户名", "机构代码", "组织机构代码", "卡号"}
					if base.InStringSlice(col.Title, titles) {
						varChars := []rune(val)
						vlen := len(varChars)
						omits := make([]rune, vlen)
						for i := 2; i < vlen; i++ {
							omits[i] = '*'
						}
						toVal = string(varChars[:2]) + string(omits)
					}
				}
			}
		}
		dataMap[col.Name] = toVal
		for k, st := range sts {
			str := row[st.colIdx].String
			if str == "" && base.InStringSlice(col.Name, lr.config.Groups[k].ExcludeCols) {
				dataMap[col.Name] = ""
			}
			if st.colIdx == i && toVal == "" {
				if caption := row[st.captionColIdx].String; caption != "" {
					dataMap[col.Name] = row[st.captionColIdx].String + st.suffix
					break
				}
			}
		}
		i++
	}
	return
}
